/*
	EXEC SP_DIRECT_MARGIN_REPORT '0', 0, 0, 'ALL', NULL, 1, 'SHIPMENT', '04/01/2007', NULL
*/
ALTER PROC SP_DIRECT_MARGIN_REPORT  @COST_CODE VARCHAR(50),
				@PROCESSSTEP_ID INT,
				@CUSTOMER_ID INT,
				@STATUS VARCHAR(50), --BACKLOG, BOOKING, SHIPMENT, BACKLOG_SHIPMENT, ALL
				@DM_PERCENTAGE FLOAT,
				@DM_PERCENTAGE_OPTR INT, --0=LESS THAN, 1=GREATER THAN, 2=EQUAL TO
				@DATE_TYPE VARCHAR(20), --BOOKING, SCHEDULE, SHIPMENT
				@FROM_DATE DATETIME,
				@TO_DATE DATETIME
				--@GROUP_BY VARCHAR(20), --CUSTOMER, PRODUCT, COST_CODE
				--@SORT_BY VARCHAR(50), --VALUE, ALPHABET
				--@ORDER_BY VARCHAR(20) --ASCENDING, DESCENDING
AS
BEGIN
	CREATE TABLE #RESULTS_TABLE
	(
		SALESORDER_ID INT, 
		SALESORDER_NO VARCHAR(50), 
		CUSTOMER_ID INT,
		CUSTOMER_NAME VARCHAR(65),
		PROCESSSTEP_ID INT, 
		PART_NUMBER VARCHAR(50), 
		COST_CODE VARCHAR(50), 
		COOL_QTY INT, 
		SHIPPED_QTY INT, 
		REMAINING_QTY INT,
		COOL_UNIT_COST NUMERIC(18,4), 
		AMOUNT NUMERIC(18,4), 
		COST_CODE_COST NUMERIC(18,4), 
		DIFF_COST NUMERIC(18,4), 
		DIFF_COST_PERCENTAGE NUMERIC(18,4),
		--SO_BOOKING_DATE DATETIME,
		COOL_REQUESTED_DATE DATETIME, 
		COOL_SCHEDULE_DATE DATETIME, 
		SHIP_DATE DATETIME
	)

	CREATE TABLE #LINE_ITEM_DETAILS
	( 
		LINEITEM_ID INT,
		SALESORDER_ID INT, 
		SALESORDER_NO VARCHAR(50), 
		CUSTOMER_ID INT,
		CUSTOMER_NAME VARCHAR(65),
		PROCESSSTEP_ID INT, 
		PART_NUMBER VARCHAR(50), 
		COST_CODE VARCHAR(50), 
		COOL_QTY INT, 
		SHIPPED_QTY INT, 
		REMAINING_QTY INT,
		COOL_UNIT_COST NUMERIC(18,4), 
		AMOUNT NUMERIC(18,4), 
		COST_CODE_COST NUMERIC(18,4), 
		SO_BOOKING_DATE DATETIME,
		COOL_REQUESTED_DATE DATETIME, 
		COOL_SCHEDULE_DATE DATETIME, 
		SHIP_DATE DATETIME 
	)
	
	DECLARE @LINE_ITEM_DETAILS_STR NVARCHAR(4000)

	SET @LINE_ITEM_DETAILS_STR = N'
	  INSERT INTO #LINE_ITEM_DETAILS 
		SELECT COOL.LINEITEM_ID, 
			SO.SALESORDER_ID, 
			SO.SALESORDER_NO, 
			C.CUSTOMER_ID, 
			C.C_NAME, 
			P.PROCESSSTEP_ID, 
			P.PART_NUMBER, 
			CCT.COST_CODE,
			COOL.COOL_QTY, 
			ISNULL( XYZ.TOTAL, 0) AS SHIPPED_QTY,
			( COOL.COOL_QTY - ISNULL( XYZ.TOTAL, 0 ) ) AS REMAINING_QTY, 		
			COOL.COOL_UNIT_COST,
			(COOL.COOL_QTY * COOL.COOL_UNIT_COST) AS AMOUNT,
			(CASE WHEN (SELECT COUNT(*) FROM PROCESS_STEP_TRAVERSE_TABLE PSTT, PROCESS_STEP PS WHERE PS.PROCESSSTEP_ID = PSTT.PROCESSSTEP_ID AND PSTT.NEXT_PROCESSSTEP_ID = P.PROCESSSTEP_ID AND PS_TYPE = ''TAPE & REEL'')>0 THEN CCT.TR_COST ELSE CCT.FG_COST END) AS COST_CODE_COST, 
			SO.SO_BOOKING_DATE,
			COOL.COOL_REQUESTED_DATE, 
			ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) AS COOL_SCHEDULE_DATE,
			XYZ.SHIP_DATE AS SHIP_DATE
		FROM
		(
			(
				(
					CUSTOMER_OPEN_ORDER_LINEITEM COOL 
					LEFT OUTER JOIN 
					(
						SELECT LINEITEM_ID, SUM( CSLIT_QTY_SHIPPED ) AS TOTAL, MAX(CST.CST_CONFIRMED_ON) AS SHIP_DATE
						FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT WHERE CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID AND CSLIT_STATUS = ''SHIPPED'' GROUP BY LINEITEM_ID
					) 
					XYZ ON COOL.LINEITEM_ID = XYZ.LINEITEM_ID
					INNER JOIN SALES_ORDER SO ON COOL.SALESORDER_ID = SO.SALESORDER_ID AND SO.SO_IS_SAMPLE = 1
				)
				INNER JOIN PRODUCT P ON COOL.PRODUCT_ID = P.PRODUCT_ID
				LEFT OUTER JOIN COST_CODE_TABLE CCT ON P.COST_CODE = CCT.COST_CODE AND CCT.COST_CODE IS NOT NULL AND DATEPART( "qq", CURRENT_TIMESTAMP ) = CCT.QTR AND YEAR( CURRENT_TIMESTAMP ) = CCT.YEAR 
			)
			INNER JOIN CUSTOMER C ON SO.CUSTOMER_ID = C.CUSTOMER_ID
		)
		WHERE P.COST_CODE IS NOT NULL AND CCT.COST_CODE IS NOT NULL'
	IF ( @COST_CODE IS NOT NULL AND @COST_CODE != '0' )
	 BEGIN
		SET @LINE_ITEM_DETAILS_STR = @LINE_ITEM_DETAILS_STR + ' AND CCT.COST_CODE = ''' + @COST_CODE + ''''
	 END
	IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID > 0 )
	 BEGIN
		SET @LINE_ITEM_DETAILS_STR = @LINE_ITEM_DETAILS_STR + ' AND P.PROCESSSTEP_ID = ''' + CONVERT(VARCHAR,@PROCESSSTEP_ID) + ''''
	 END
	IF ( @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID > 0 )
	 BEGIN
		SET @LINE_ITEM_DETAILS_STR = @LINE_ITEM_DETAILS_STR + ' AND SO.CUSTOMER_ID = ''' + CONVERT(VARCHAR,@CUSTOMER_ID) + ''''
	 END
	--PRINT @LINE_ITEM_DETAILS_STR
	EXEC (@LINE_ITEM_DETAILS_STR)
	--SELECT * FROM #LINE_ITEM_DETAILS
	

	DECLARE @FINAL_QUERY NVARCHAR(2000)
	DECLARE @FINAL_COMMON_QUERY NVARCHAR(1000)
	DECLARE @GROUP_BY_STR NVARCHAR(500)
	SET @GROUP_BY_STR = N' GROUP BY SALESORDER_ID, SALESORDER_NO, CUSTOMER_ID, CUSTOMER_NAME, PROCESSSTEP_ID, PART_NUMBER, COST_CODE'
	SET @FINAL_COMMON_QUERY = N'
	    INSERT INTO #RESULTS_TABLE
		SELECT 
			SALESORDER_ID, 
			SALESORDER_NO, 
			CUSTOMER_ID, 
			CUSTOMER_NAME, 
			PROCESSSTEP_ID, 
			PART_NUMBER, 
			COST_CODE,
			SUM( COOL_QTY ) AS COOL_QTY, 
			SUM( SHIPPED_QTY ) AS SHIPPED_QTY,
			SUM( REMAINING_QTY ) AS REMAINING_QTY, 		
			AVG(COOL_UNIT_COST) AS COOL_UNIT_COST,
			( SUM( COOL_QTY )  * AVG(COOL_UNIT_COST) ) AS AMOUNT,
			AVG(COST_CODE_COST) AS COST_CODE_COST, 
			( AVG( COOL_UNIT_COST ) - AVG( COST_CODE_COST ) ) AS DIFF_COST,
			CASE AVG( COOL_UNIT_COST ) WHEN 0 THEN ( AVG( COOL_UNIT_COST ) - AVG( COST_CODE_COST ) ) ELSE ( ( (AVG( COOL_UNIT_COST ) - AVG( COST_CODE_COST )) / AVG( COOL_UNIT_COST ) ) * 100  ) END AS DIFF_COST_PERCENTAGE,
			MAX(COOL_REQUESTED_DATE) AS COOL_REQUESTED_DATE, 
			MAX(COOL_SCHEDULE_DATE) AS COOL_SCHEDULE_DATE,
			MAX(SHIP_DATE) AS SHIP_DATE
		FROM #LINE_ITEM_DETAILS WHERE 1=1 '

	IF ( @STATUS = 'BACKLOG' )
	 BEGIN
		SET @FINAL_QUERY = @FINAL_COMMON_QUERY
		SET @FINAL_QUERY = @FINAL_QUERY + ' AND REMAINING_QTY > 0'		
		EXEC (@FINAL_QUERY+@GROUP_BY_STR)
	 END
	IF ( @STATUS = 'BOOKING' )
	 BEGIN
		SET @FINAL_QUERY = @FINAL_COMMON_QUERY
		IF ( @DATE_TYPE = 'BOOKING' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL AND LEN(@TO_DATE) > 0 )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		ELSE --SCHEDULE
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL AND LEN(@TO_DATE) > 0 )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		EXEC (@FINAL_QUERY+@GROUP_BY_STR)
	 END
	IF ( @STATUS = 'SHIPMENT' )
	 BEGIN
		SET @FINAL_QUERY = @FINAL_COMMON_QUERY
		SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIPPED_QTY > 0'
		IF ( @DATE_TYPE = 'BOOKING' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		ELSE IF ( @DATE_TYPE = 'SCHEDULE' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		ELSE IF ( @DATE_TYPE = 'SHIPMENT' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIP_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIP_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		--PRINT @FINAL_QUERY
		EXEC (@FINAL_QUERY+@GROUP_BY_STR)
	 END
	IF ( @STATUS = 'BACKLOG_SHIPMENT' )
	 BEGIN
		SET @FINAL_QUERY = @FINAL_COMMON_QUERY
		SET @FINAL_QUERY = @FINAL_QUERY + ' AND REMAINING_QTY > 0 OR ( SHIPPED_QTY > 0'
		IF ( @DATE_TYPE = 'BOOKING' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		ELSE IF ( @DATE_TYPE = 'SCHEDULE' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59AM ' + ''''
			 END
		 END
		ELSE IF ( @DATE_TYPE = 'SHIPMENT' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIP_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIP_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		SET @FINAL_QUERY = @FINAL_QUERY + ' ) '
		EXEC (@FINAL_QUERY+@GROUP_BY_STR)
	 END
	IF ( @STATUS IS NULL OR @STATUS = '0' OR @STATUS = 'ALL' ) --ALL
	 BEGIN
		SET @FINAL_QUERY = @FINAL_COMMON_QUERY
		IF ( @DATE_TYPE = 'BOOKING' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		ELSE IF ( @DATE_TYPE = 'SCHEDULE' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		ELSE IF ( @DATE_TYPE = 'SHIPMENT' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIP_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIP_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		EXEC (@FINAL_QUERY+@GROUP_BY_STR)
	 END	
	
	IF ( @DM_PERCENTAGE IS NOT NULL )
	 BEGIN
		IF ( @DM_PERCENTAGE_OPTR = 0 )
		 BEGIN
			SELECT * FROM #RESULTS_TABLE WHERE DIFF_COST_PERCENTAGE < @DM_PERCENTAGE
		 END
		ELSE IF ( @DM_PERCENTAGE_OPTR = 1 )
		 BEGIN
			SELECT * FROM #RESULTS_TABLE WHERE DIFF_COST_PERCENTAGE > @DM_PERCENTAGE
		 END
		ELSE
		 BEGIN
			SELECT * FROM #RESULTS_TABLE WHERE DIFF_COST_PERCENTAGE = @DM_PERCENTAGE
		 END
	 END
	ELSE
	 BEGIN
		SELECT * FROM #RESULTS_TABLE
	 END
	
END